﻿CREATE PROCEDURE [dbo].[sp_Get_OrderSummary]
	@Year varchar(4), 
	@Month varchar(2)
AS
	if object_id('tempdb..#OrderSummary') is not null 
	drop table #OrderSummary	
	create table #OrderSummary
	(
		 AccountID varchar(4) COLLATE database_default
		,LeadCount int
	)

	insert into #OrderSummary
	select o.AccountID, 
	sum(case when oz.Type = 2 then 0 else zi.ActiveStopCount end) + sum(case when oz.Type = 2 then 0 else zi.LapsedStopCount end) as LeadCount
	from dbo.Orders o
	inner join dbo.OrderZip oz on o.OrderID = oz.OrderID
	and o.PeroidYear = @Year and o.PeroidMonth = @Month and o.IsPurchased = 1
	inner join dbo.ZipInfo zi on zi.ZipID = oz.ZipID
	group by o.OrderID, o.AccountID

	select a.FirstName + ' ' + a.LastName, a.AgentNumber, (a.AgentNumber * 200) as TargetCount, 
	(case when os.LeadCount is not null then os.LeadCount else 0 end) as ActualCount
	from dbo.Accounts a
	left join #OrderSummary os
	on os.AccountID = a.AccountID
	where a.AccountType = 0 and a.Status <> 2
	union all
	select 'Grand Total', sum(a.AgentNumber), sum(a.AgentNumber * 200), 
	sum((case when os.LeadCount is not null then os.LeadCount else 0 end))
	from dbo.Accounts a
	left join #OrderSummary os
	on os.AccountID = a.AccountID
	where a.AccountType = 0 and a.Status <> 2